/** 
Copyright (c) 2010, Sheikh Abdul Wahid Ahmed
Details @ http://extremecodeworld.codeplex.com/license
**/

using System;
using EXtremecode.Utils;
using System.Data;
using System.Data.SqlServerCe;
using EXtremecode.DataAccessLayer; 
using System.Collections;
using System.Text;
using EXtremecode.Common;
using System.Data.Common;
using System.Collections.Generic;



namespace EXtremecode.DataAccessLayer
{
	/// <summary>
	/// Summary description for SQLServerDataAccessor.
	/// </summary>
	public class SQLServerCEDataAccessor:DataAccessor
	{
		#region SYSTEM QUERIES
		internal override string QUERY_Table 
		{ 
			get 
			{
				return	"SELECT TABLE_NAME AS Name  FROM  INFORMATION_SCHEMA.TABLES";
			}
		}
        internal override string QUERY_View 
		{
			get 
			{
				return "SELECT TABLE_NAME AS Name  FROM  INFORMATION_SCHEMA.TABLES where 1=2";
			}
		}
		internal override string QUERY_TableColumn 
		{
			get 
			{
                return "SELECT     Table_Name, COLUMN_NAME AS ColumnName,1 as TypeCode, " + 
					"CHARACTER_MAXIMUM_LENGTH AS Length FROM INFORMATION_SCHEMA.COLUMNS ";
					
			}
		}
		#endregion

protected override DataTable GetRelationInfo()
        {
            string query = string.Format(@"
SELECT        refcons.CONSTRAINT_NAME AS FK_NAME ,refcons.CONSTRAINT_TABLE_NAME AS FK_TABLE_NAME
				,refcons.UNIQUE_CONSTRAINT_TABLE_NAME AS PK_TABLE_NAME
				, fkconsColunms.COLUMN_NAME AS FK_COLUMN_NAME, pkconsColunms.COLUMN_NAME AS PK_COLUMN_NAME
FROM            INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS refcons
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkconsColunms
on refcons.CONSTRAINT_NAME =  fkconsColunms.CONSTRAINT_NAME

inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkconsColunms
on refcons.UNIQUE_CONSTRAINT_NAME =  pkconsColunms.CONSTRAINT_NAME 
and fkconsColunms.ORDINAL_POSITION = pkconsColunms.ORDINAL_POSITION"
                );

            return doExecuteFillQuery(query,false);
        
        }		
	
        public override string DBNow
        {
            get { return "getDate()"; }
        }
		

		public SQLServerCEDataAccessor(string sAccessorName,string sConnectionURL):this(sAccessorName,sConnectionURL,true)
		{
		}

        public SQLServerCEDataAccessor(string sAccessorName, string sConnectionURL, bool validationRequired)
            : base(sAccessorName, sConnectionURL,validationRequired)
        {

        }




        internal override string AdjustUDType4BinaryField(int intTypeCode, string strUDType)
		{
			if(intTypeCode == 173 || intTypeCode == 165)strUDType="short" + strUDType;//173:binary  165:varbinary
			return strUDType;

		}
		
		
		
		public override string GetParameterName(string paramName)
		{
			return "@" + paramName;
		}
		public override IDbCommand CreateCommand()
		{
			
			return new SqlCeCommand(); 
		}

        internal override IDbDataAdapter CreateDataAdapter()
		{
            SqlCeDataAdapter adapter = new SqlCeDataAdapter();
			adapter.RowUpdating +=new SqlCeRowUpdatingEventHandler(Adapter_RowUpdating);
            adapter.RowUpdated += new SqlCeRowUpdatedEventHandler(Adapter_RowUpdated);
			return  adapter;
		}

		public override IDataParameter GetParameter(string name,string type)
		{
            SqlCeParameter param = new SqlCeParameter(name, (SqlDbType)int.Parse(FreeHash.SQLDBType[type]), 0); 
			return param;
		}
		

		
		protected override IDbConnection  CreateConnection()
		{
            SqlCeConnection conn = new SqlCeConnection( string.Format(m_sConnectionURL,System.AppDomain.CurrentDomain.BaseDirectory));
			conn.StateChange +=new StateChangeEventHandler(base.Connection_StateChange);
			return conn;

		}

        private void Adapter_RowUpdating(object sender, SqlCeRowUpdatingEventArgs e)
        {
            base.Adapter_RowUpdating(sender, e);
            e.Command.CommandText = e.Command.CommandText.Replace(";", "--;");
        }
        private IEnumerable<IDbCommand> GetInternalCommands(IDbCommand mainCommand, bool returnMainCommandItself)
        {

            if (returnMainCommandItself)
            {
                try
                {
                    mainCommand.CommandText = mainCommand.CommandText.Replace(";", "--;");
                    yield return mainCommand;
                }
                finally
                {
                    mainCommand.CommandText = mainCommand.CommandText.Replace("--;", ";");
                }
            }


            string[] currentSelectCommandsText = mainCommand.CommandText.Split(';');
            if (currentSelectCommandsText.Length > 1
                && currentSelectCommandsText[1].Trim() != string.Empty)
            {
                //we have only maximum two command in single statement.
                //if second command is defined then create its command object and return it to caller. 


                IDbCommand cmdTemp = (mainCommand is ServiceCommand) ?
                    (mainCommand as ServiceCommand).Accessor.CreateCommand() :
                    CreateCommand();

                cmdTemp.CommandText = currentSelectCommandsText[1]; //second command; 
                cmdTemp.Connection = mainCommand.Connection;
                cmdTemp.Transaction = mainCommand.Transaction;

                foreach (IDbDataParameter param in mainCommand.Parameters)
                {
                    //we are considering, where clause of this comand contain the reference of key params
                    //so we are add these param into new created command.

                    if (param.ParameterName.EndsWith(ColumnSuffix.KeyParam))
                    {
                        IDbDataParameter newParam = cmdTemp.CreateParameter();
                        Utils.DataAccessorUtil.MakeCloneOfParameter(param, newParam);
                        cmdTemp.Parameters.Add(newParam);
                    }
                    else
                    {
                        break; //only top parameter are expected as a key param. 
                    }
                }

                yield return cmdTemp;

            }



            yield break;

        }
        public override IEnumerable<IDbCommand> GetInternalCommands(IDbCommand mainCommand)
        {
            return GetInternalCommands(mainCommand, true);
        }


        private void Adapter_RowUpdated(object sender, SqlCeRowUpdatedEventArgs e)
        {
            base.Adapter_RowUpdated(sender, e);

            e.Command.CommandText = e.Command.CommandText.Replace("--;", ";");
            IDbDataAdapter da = sender as IDbDataAdapter;


            if ((e.Command == da.UpdateCommand || e.Command == da.InsertCommand) &&
                e.Errors == null)
            {
                //should be update or insert command
                //no error should be occoured

                foreach (IDbCommand cmd in GetInternalCommands(e.Command, false))
                {
                    using (SqlCeDataAdapter daTemp = new SqlCeDataAdapter())
                    {
                        daTemp.SelectCommand =(SqlCeCommand)cmd;
                        using (DataTable dt = new DataTable())
                        {
                            daTemp.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                                DBUtil.CopyDataRow(dt.Rows[0], e.Row);
                            }
                        }
                    }
                    break;

                }

            }
        }

        internal override string GeneratePagingQuery(string selectQuery, string sortedBy, bool isSortingTypeAscending, int pageNumber, int pageSize, long? totalRecords)
        {
            if (sortedBy.Trim() == string.Empty)
            {
                throw new Exception("[UC] Sorted Column should be defined");
            }

            string sortingType = (isSortingTypeAscending) ? " ASC " : " DESC ";
            long bottomRowNum = pageNumber * pageSize;
            long topRowNum = bottomRowNum - pageSize + 1;


            //enclose original query;
            string firstQuery = string.Format(@"Select * from 
                (
                    select query1.*,row_number() over(order by {0}) as row_num  from
                    (
                        {1}
                    ) query1
                ) where row_num >= {2} and row_num <= {3}",
                sortedBy.Replace(",", sortingType + ",") + sortingType,
                selectQuery, topRowNum, bottomRowNum);


            return firstQuery;
        }
    }
}
